package com.hnkywl.jip.dao;

import com.hnkywl.jip.utils.VoucherMasterData;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-2-26
 * Time: 下午4:24
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class OtherVoucherDao extends SimpleHibernateTemplate {
    /**
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param companyId 公司ID
     * @param operator  操作人
     * @return
     */
    public Page<Object[]> findOtherVoucher(Date startTime, Date endTime, Integer companyId, Integer bill,
                                           Integer status, Integer voucher, String operator, Integer limit, Integer start) {
        // sql查询字段
        //单据号码 收款日期 收款类型 客户名称 收款金额 票据号码 入帐银行 类型 审核人 公司名称 凭证号
        String sqlColumn = "select b.jl_acc1id,zddt=convert(varchar(10),b.zddt,102), " +
                " typename=isnull((select top 1 csign from t_cw_sys_accvouch2 where jl_acc1id=b.jl_acc1id),''),b.compid, " +
                " compname=dbo.gf_compid_shortname(b.compid),createempname=dbo.gf_manid_name(b.createempid), " +
                " createdt=convert(varchar(10),b.createdt,102),b.note,u8ino_id=NULLIF(b.u8ino_id,0), " +
                " isreset=(case when b.u8insflag=2 or b.u8delflag=2 then 1 else 0 end),isedit=1,issyn=isnull(b.isedit,0) ";


        String sqlCondition =
                " from ( " +
                        " select a.jl_acc1id from t_cw_sys_accvouch1 a " +
                        " left join t_crm_man b on a.createempid=b.manid " +
                        " where a.zddt between :dt1 and :dt2 " +
                        (status != null && status != 0 ? " and (:status=1 and a.u8ino_id=0 or :status=2 and a.u8ino_id>0) " : "") +
                        (companyId != null ? " and (:compid=-1 or a.compid=:compid) " : "") +
                        (bill != null ? " and (:jl_acc1id=-1 or a.jl_acc1id=:jl_acc1id) " : "") +
                        (voucher != null ? " and (:u8ino_id=-1 or a.u8ino_id=:u8ino_id) " : "") +
                        " and a.isinit=0 " +
                        (StringUtils.isNotEmpty(operator) ? " and (:czr='-1' or b.name=:czr) " : "") +
                        ") a inner join t_cw_sys_accvouch1 b on a.jl_acc1id=b.jl_acc1id ";

        //获取总记录数
        SQLQuery query = createSQLQuery("select count(1)" + sqlCondition);

        query.setDate("dt1", startTime);
        query.setDate("dt2", endTime);

        if (status != null && status != 0)
            query.setInteger("status", status);
        if (companyId != null)
            query.setInteger("compid", companyId);
        if (bill != null)
            query.setInteger("jl_acc1id", bill);
        if (voucher != null)
            query.setInteger("u8vouchid", voucher);
        if (StringUtils.isNotEmpty(operator))
            query.setString("czr", operator);
        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn + sqlCondition);

            query.setDate("dt1", startTime);
            query.setDate("dt2", endTime);

            if (status != null && status != 0)
                query.setInteger("status", status);
            if (companyId != null)
                query.setInteger("compid", companyId);
            if (bill != null)
                query.setInteger("jl_acc1id", bill);
            if (voucher != null)
                query.setInteger("u8vouchid", voucher);
            if (StringUtils.isNotEmpty(operator))
                query.setString("czr", operator);
            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }

    /**
     * 其它凭证主表信息
     *
     * @param id 收付款ID
     */
    public List getVoucherMaster(Integer id) {
        String sql = "select vchrtypecode=v.name,c.company_name,c.company_code from t_cw_sys_accvouch1 t,t_jiuqi_company c,V_CW_SYS_CSIGN v where t.compid=c.company_id and v.id=t.isignseq and t.jl_acc1id=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getVoucher(Integer id) {
        String vchrtypecode = null;
        String sql = "{call sp_jiuqi_voucher_receipt_payment(?,?)}";
        List<Object[]> list = createSQLQuery(sql, id, vchrtypecode).list();
        return list;
    }

    public void saveVoucherMaster(VoucherMasterData vm, Integer cnt) {
        String sql = "insert into t_cw_sys_accvouch1(compid,note,createempid,createdt,u8ino_id,zddt,u8insflag,u8delflag,u8cnt,isignseq,iperiod,isinit) " +
                " values(?,?,?,?,0,?,1,1,?,?,?,?)";
        Integer month = vm.getCreateTime().getMonth() + 1;
        createSQLQuery(sql, vm.getCompanyId(), vm.getRemarks(), vm.getOperatorId(), new Date(),
                vm.getCreateTime(), cnt, vm.getVoucherTypeCodeId(), month, 0).executeUpdate();
    }

    public void saveVoucherItem() {
        String sql = "insert into t_cw_sys_accvouch2(iperiod,csign,isignseq,ino_id,inid,dbill_date,idoc,cbill,ccheck,cbook,ibook,ccashier,iflag,ctext1,ctext2," +
                "cdigest,ccode,cexch_name,md,mc,md_f,mc_f,nfrat,nd_s,nc_s," +
                "csettle,cn_id,dt_date,cdept_id,cperson_id,ccus_id,csup_id,citem_id,citem_class,cname,ccode_equal," +
                "iflagbank,iflagPerson,bdelete,coutaccset,ioutyear,coutsysname,coutsysver,doutbilldate,ioutperiod," +
                "coutsign,coutno_id,doutdate,coutbillsign,coutid,bvouchedit,bvouchAddordele,bvouchmoneyhold," +
                "bvalueedit,bcodeedit,ccodecontrol,bPCSedit,bDeptedit,bItemedit,bCusSupInput,dReceive," +
                "cWLDZFlag,dWLDZTime,bFlagOut,iBG_OverFlag,cBG_Auditor,dBG_AuditTime,cBG_AuditOpinion," +
                "bWH_BgFlag,ssxznum,CErrReason,BG_AuditRemark,cBudgetBuffer,iBG_ControlResult,NCVouchID,daudit_date," +
                "cBankReconNo,jl_supid,jl_custid,jl_deptid,jl_empid,jl_citemname,jl_acc1id)" +
                "values(" +
                "#iperiod:integer#," +
                "#csign:varchar#," +
                "#isignseq:integer#," +
                "0," +
                "#inid:integer#," +
                "#dbill_date:varchar#," +
                "#idoc:integer#," +
                "#cbill:varchar#," +
                "#ccheck:varchar#," +
                "#cbook:varchar#," +
                "#ibook:integer#," +
                "#ccashier:varchar#," +
                "#iflag:integer#," +
                "#ctext1:varchar#," +
                "#ctext2:varchar#," +
                "#cdigest:varchar#," +
                "#ccode:varchar#," +
                "#cexch_name:varchar#," +
                "#md:double#," +
                "#mc:double#," +
                "#md_f:double#," +
                "#mc_f:double#," +
                "#nfrat:double#," +
                "#nd_s:double#," +
                "#nc_s:double#," +
                "#csettle:varchar#," +
                "#cn_id:string#," +
                "#dt_date:varchar#," +
                "#cdept_id:varchar#," +
                "#cperson_id:varchar#," +
                "#ccus_id:varchar#," +
                "#csup_id:varchar#," +
                "#citem_id:varchar#," +
                "#citem_class:varchar#," +
                "#cname:varchar#," +
                "#ccode_equal:varchar#," +
                "#iflagbank:integer#," +
                "#iflagPerson:integer#," +
                "#bdelete:integer#," +
                "#coutaccset:varchar#," +
                "#ioutyear:integer#," +
                "#coutsysname:varchar#," +
                "#coutsysver:varchar#," +
                "#doutbilldate:varchar#," +
                "#ioutperiod:integer#," +
                "#coutsign:varchar#," +
                "#coutno_id:varchar#," +
                "#doutdate:varchar#," +
                "#coutbillsign:varchar#," +
                "#coutid:varchar#," +
                "#bvouchedit:integer#," +
                "#bvouchAddordele:integer#," +
                "#bvouchmoneyhold:integer#," +
                "#bvalueedit:integer#," +
                "#bcodeedit:integer#," +
                "#ccodecontrol:varchar#," +
                "#bPCSedit:integer#," +
                "#bDeptedit:integer#," +
                "#bItemedit:integer#," +
                "#bCusSupInput:integer#," +
                "#dReceive:varchar#," +
                "#cWLDZFlag:varchar#," +
                "#dWLDZTime:varchar#," +
                "#bFlagOut:integer#," +
                "#iBG_OverFlag:integer#," +
                "#cBG_Auditor:varchar#," +
                "#dBG_AuditTime:varchar#," +
                "#cBG_AuditOpinion:varchar#," +
                "#bWH_BgFlag:integer#," +
                "#ssxznum:integer#," +
                "#CErrReason:varchar#," +
                "#BG_AuditRemark:varchar#," +
                "#cBudgetBuffer:varchar#," +
                "#iBG_ControlResult:integer#," +
                "#NCVouchID:varchar#," +
                "#daudit_date:varchar#," +
                "#cBankReconNo:varchar#," +
                "#jl_supid:integer#," +
                "#jl_custid:integer#," +
                "#jl_deptid:integer#," +
                "#jl_empid:integer#," +
                "#jl_citemname:varchar#," +
                "#jl_acc1id:integer#)";
    }
}
